﻿using System;
using System.Data;
using System.Data.SqlClient;
using LogExceptions;


namespace BusinessLayer
{
    public sealed class SQLHelper
    {
        #region Utility Methods & Constructors
        public SQLHelper()
        {
        }

        public static string GetConnection()
        {
            string strCon = XmlConec.GetSQLConection();
            return strCon;
        }

        ~SQLHelper()
        {
        }

        private static void AttachParameters(SqlCommand command, SqlParameter[] cmdParams)
        {
            if (command == null)
                throw new ArgumentNullException("command");
            if (cmdParams != null)
            {
                foreach (SqlParameter p in cmdParams)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                            p.Value = DBNull.Value;
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        private static void AssignParameterValues(SqlParameter[] cmdParams, DataRow dataRow)
        {
            if ((cmdParams == null) || (dataRow == null))
                return;

            int i = 0;
            foreach (SqlParameter commandParameter in cmdParams)
            {
                if (commandParameter.ParameterName == null ||
                    commandParameter.ParameterName.Length <= 1)
                    throw new Exception(
                        string.Format(
                            "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
                            i, commandParameter.ParameterName));
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        private static void AssignParameterValues(SqlParameter[] cmdParams, object[] parameterValues)
        {
            if ((cmdParams == null) || (parameterValues == null))
                return;

            if (cmdParams.Length != parameterValues.Length)
                throw new ArgumentException("Parameter count does not match Parameter Value count.");

            for (int i = 0, j = cmdParams.Length; i < j; i++)
            {
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                        cmdParams[i].Value = DBNull.Value;
                    else

                        cmdParams[i].Value = paramInstance.Value;
                }
                else if (parameterValues[i] == null)
                    cmdParams[i].Value = DBNull.Value;
                else
                    cmdParams[i].Value = parameterValues[i];
            }
        }

        public static void PrepareCommand(SqlConnection sqlCon, SqlCommand command, SqlTransaction sqlTrans, string commandText, SqlParameter[] cmdParams, out bool mustCloseCon)
        {
            try
            {
                if (command == null) throw new ArgumentNullException("command");
                if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

                if (sqlCon.State != ConnectionState.Open)
                {
                    mustCloseCon = true;
                    try
                    {
                        //sqlCon.Open();
                    }
                    catch (Exception e)
                    {
                        string msg = "Database connection Error! Maybe your Database is not runing or database connection string is mistake?";
                        throw new Exception(msg, e);
                    }
                }
                else
                    mustCloseCon = false;


                command.Connection = sqlCon;
                command.CommandText = commandText;

                if (sqlTrans != null)
                {
                    if (sqlTrans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                    command.Transaction = sqlTrans;
                }

                command.CommandType = CommandType.StoredProcedure;
                if (cmdParams != null)                
                    AttachParameters(command, cmdParams);                
                return;
            }
            catch
            {
                mustCloseCon = true;
            }
        }
        #endregion

        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, (SqlParameter[])null);
        }

        public static int ExecuteNonQuery(string commandText, params SqlParameter[] cmdParams)
        {
            SqlCommand cmd = new SqlCommand();
            bool mustCloseCon = false;
            SqlConnection sqlCon = new SqlConnection(GetConnection());
            sqlCon.Open();
            PrepareCommand(sqlCon, cmd, (SqlTransaction)null, commandText, cmdParams, out mustCloseCon);
            int retval = cmd.ExecuteNonQuery();
            sqlCon.Close();
            sqlCon.Dispose();
            cmd.Parameters.Clear();
            return retval;
        }                
        #endregion
                
        #region BooleanReader
        private enum SqlConnectionOwnership
        {
            /// <summary>Connection is owned and managed by SqlHelper</summary>
            Internal,
            /// <summary>Connection is owned and managed by the caller</summary>
            External
        }

        public static bool BoooleanReader(string commandText)
        {
            return BoooleanReader(commandText, (SqlParameter[])null);
        }

        public static bool BoooleanReader(string commandText, params SqlParameter[] cmdParams)
        {
            return BoooleanReader(null, commandText, cmdParams, SqlConnectionOwnership.Internal);
        }

        private static bool BoooleanReader(SqlTransaction sqlTrans, string commandText, SqlParameter[] cmdParams, SqlConnectionOwnership connectionOwnership)
        {
            bool mustCloseCon = false, bFound = false;
            SqlCommand cmd = new SqlCommand();
            SqlConnection sqlCon = new SqlConnection(GetConnection());
            sqlCon.Open();
            PrepareCommand(sqlCon, cmd, sqlTrans, commandText, cmdParams, out mustCloseCon);
            SqlDataReader dbReader;

            if (connectionOwnership == SqlConnectionOwnership.External)
                dbReader = cmd.ExecuteReader();
            else
                dbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            if (dbReader.Read())
                bFound = true;

            bool canClear = true;
            foreach (SqlParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }

            if (canClear)
                cmd.Parameters.Clear();

            dbReader.Dispose();
            sqlCon.Close();
            sqlCon.Dispose();
            return bFound;
        }
        #endregion

        #region StringReader
        public static string StringReader(string commandText, string strField)
        {
            return StringReader(commandText, strField, (SqlParameter[])null);
        }

        public static string StringReader(string commandText, string strField, params SqlParameter[] cmdParams)
        {
            return StringReader(null, commandText, strField, cmdParams, SqlConnectionOwnership.Internal);
        }

        private static string StringReader(SqlTransaction sqlTrans, string commandText, string strField, SqlParameter[] cmdParams, SqlConnectionOwnership connectionOwnership)
        {
            bool mustCloseCon = false;
            string strResult = "";
            SqlCommand cmd = new SqlCommand();
            SqlConnection sqlCon = new SqlConnection(GetConnection());
            sqlCon.Open();
            PrepareCommand(sqlCon, cmd, sqlTrans, commandText, cmdParams, out mustCloseCon);
            SqlDataReader dbReader;

            if (connectionOwnership == SqlConnectionOwnership.External)
                dbReader = cmd.ExecuteReader();
            else
                dbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (dbReader.Read())
                strResult = dbReader[strField].ToString();

            bool canClear = true;
            foreach (SqlParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }

            if (canClear)
                cmd.Parameters.Clear();

            dbReader.Dispose();
            sqlCon.Close();
            sqlCon.Dispose();
            return strResult;
        }
        #endregion

        #region FillDataset
        public static void FillDataset(string spName, DataSet dataSet)
        {
            FillDataset(spName, dataSet, null);
        }

        public static void FillDataset(string spName, DataSet dataSet, params SqlParameter[] cmdParams)
        {
            if (cmdParams == null)
                FillDataset(null, spName, dataSet, null);
            else
                FillDataset(null, spName, dataSet, cmdParams);
        }

        private static void FillDataset(SqlTransaction sqlTrans, string spName, DataSet dataSet, params SqlParameter[] cmdParams)
        {
            if (dataSet == null)
                throw new ArgumentNullException("dataSet");
            SqlCommand command = new SqlCommand();
            bool mustCloseCon = false;
            SqlConnection sqlCon = new SqlConnection(GetConnection());            
            PrepareCommand(sqlCon, command, sqlTrans, spName, cmdParams, out mustCloseCon);            
            using (SqlDataAdapter dbAdapter = new SqlDataAdapter(command))
            {
                dbAdapter.TableMappings.Add("Table", "Table");
                dbAdapter.Fill(dataSet);
                command.Parameters.Clear();
            }
        }
        #endregion

        #region ExecuteDataset
        public static DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(commandText, (SqlParameter[])null);
        }

        public static DataSet ExecuteDataset(string commandText, params SqlParameter[] cmdParams)
        {
            SqlCommand cmd = new SqlCommand();
            bool mustCloseCon = false;
            SqlConnection sqlCon = new SqlConnection(GetConnection());            
            PrepareCommand(sqlCon, cmd, (SqlTransaction)null, commandText, cmdParams, out mustCloseCon);            
            DataSet ds;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
            }            
            return ds;
        }
        #endregion
    }
}
